﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChinaDynastyTours.Models;
using System;
using System.Threading;

namespace ChinaDynastyTours.Controls
{
    class HotelOrderCtrl
    {
        
        #region 获取宾馆预定数据

        /// <summary>
        /// 获取宾馆预定表数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID,每条行程计划对应唯一一条宾馆数据，反过来则不成立</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetHotelOrderData(int tourGroupScheduleId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT h.[HotelOrderID], 
                                                h.[ExpenseID], 
                                                h.[TourgroupID],
                                                h.[TourGroupScheduleID], 
                                                h.[ScheduleMethod], 
                                                h.[ArrivalTime], 
                                                h.[LeaveTime],
                                                h.[BreakMethod],
                                                h.[StayOver],
                                                h.[BigBedRoomNum],
                                                h.[SingleRoomNum],
                                                h.[DoubleRoomNum],
                                                h.[ThreeRoomNum],
                                                h.[AccompanyRoomNum],
                                                h.[ExtraBedNum],
                                                h.[BigBedRoomUnitPrice],
                                                h.[SingleRoomUnitPrice],
                                                h.[DoubleRoomUnitPrice],
                                                h.[ThreeRoomUnitPrice],
                                                h.[AccompanyRoomUnitPrice],
                                                h.[ExtraBedUnitPrice],
                                                h.[SpecialRoom],
                                                h.[ContractPath],
                                                e.[TypeBelong],
                                                e.[ProviderID],
                                                e.[ProviderName],
                                                e.[Budget],
                                                e.[Status],
                                                e.[ServiceName],
                                                e.[PayTotal]
                                                 FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID AND h.TourGroupScheduleID={0}", tourGroupScheduleId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetFirstHotelOrderRoomNumForTourgroup(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT [BigBedRoomNum],
                                                [SingleRoomNum],
                                                [DoubleRoomNum],
                                                [ThreeRoomNum],
                                                [AccompanyRoomNum] 
                                                FROM [fee_HotelOrder] WHERE TourgroupID='{0}'", tourgroupId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取指定团号的宾馆预订信息，用于打印
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static DataTable GetHotelOrderForPrint(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string sql = string.Format(@"SELECT 
                                                h.[ScheduleMethod], 
                                                h.[ArrivalTime], 
                                                h.[LeaveTime],
                                                h.[StayOver],
                                                h.[SpecialRoom],
                                                h.[BreakMethod],
                                                e.[ProviderName]
                                                 FROM fee_HotelOrder h,fee_Expense e WHERE h.ExpenseID=e.ExpenseID AND h.TourgroupID='{0}'AND  h.[ScheduleMethod]='{1}'order by h.[ArrivalTime]", tourGroupId, HotelScheduleMethodEnum.自订);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据团号获取指定团中各类房间的总数
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static DataTable GetHotelOrderRoomNum(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
//            string sql = string.Format(@"SELECT Sum(BigBedRoomNum) as BigBedRoomNums,
//                                        Sum(SingleRoomNum) as SingleRoomNums,
//                                        Sum(DoubleRoomNum) as DoubleRoomNums,
//                                        Sum(ThreeRoomNum) as ThreeRoomNums,
//                                        Sum(AccompanyRoomNum) as AccompanyRoomNums,
//                                        (Sum(BigBedRoomNum)+Sum(SingleRoomNum)+Sum(DoubleRoomNum)+Sum(ThreeRoomNum)+Sum(AccompanyRoomNum)) as TotalRoomNum 
//                                        FROM fee_HotelOrder WHERE TourgroupID='{0}'", tourGroupId);
            string sql = string.Format(@"SELECT TOP 1 BigBedRoomNum,
                                        SingleRoomNum,
                                        DoubleRoomNum,
                                        ThreeRoomNum,
                                        AccompanyRoomNum,
                                        BreakMethod,
                                        (BigBedRoomNum +SingleRoomNum+DoubleRoomNum+ThreeRoomNum) as TotalRoomNum 
                                        FROM fee_HotelOrder WHERE TourgroupID='{0}'", tourGroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据费用ID获取唯一预订信息
        /// </summary>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static DataTable GetHotelOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据费用ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT h.[HotelOrderID], 
                                                h.[ExpenseID], 
                                                h.[TourgroupID],
                                                h.[TourGroupScheduleID], 
                                                h.[ScheduleMethod], 
                                                h.[ArrivalTime], 
                                                h.[LeaveTime],
                                                h.[BreakMethod],
                                                h.[StayOver],
                                                h.[BigBedRoomNum],
                                                h.[SingleRoomNum],
                                                h.[DoubleRoomNum],
                                                h.[ThreeRoomNum],
                                                h.[AccompanyRoomNum],
                                                h.[ExtraBedNum],
                                                h.[BigBedRoomUnitPrice],
                                                h.[SingleRoomUnitPrice],
                                                h.[DoubleRoomUnitPrice],
                                                h.[ThreeRoomUnitPrice],
                                                h.[AccompanyRoomUnitPrice],
                                                h.[ExtraBedUnitPrice],
                                                h.[SpecialRoom],
                                                h.[ContractPath],
                                                e.[TypeBelong],
                                                e.[ProviderID],
                                                e.[ProviderName],
                                                e.[Budget],
                                                e.[Status],
                                                e.[ServiceName],
                                                e.[PayTotal]
                                                FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID AND e.ExpenseID={0}", expenseId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 宾馆预订信息的增、删、改
        /// <summary>
        /// 根据指定的行程计划号，宾馆预定和费用信息数据创建
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        internal static int CreateHotelOrderAndExpenseData(ExpenseEntity expenseEntity, HotelOrderEntity hotelEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expenseEntity.TourgroupID,
                                        expenseEntity.ProviderID,
                                        expenseEntity.ProviderName,
                                        expenseEntity.ServiceName,
                                        expenseEntity.Budget,
                                        expenseEntity.PayTotal,
                                        expenseEntity.ApplyPayTotal,
                                        expenseEntity.AdjustAmount,
                                        expenseEntity.Status,
                                        expenseEntity.TypeBelong,
                                        expenseEntity.ApplyPay,
                                        expenseEntity.PayStatus,
                                        expenseEntity.TourgroupName,
                                        expenseEntity.CreateByUserName,
                                        expenseEntity.TourGroupScheduleID,
                                        expenseEntity.CreateTime,
                                        expenseEntity.ExpenseTypeID,
                                        expenseEntity.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlHotel = string.Format(@"INSERT INTO fee_HotelOrder (
                                        [ExpenseID], [TourgroupID],
                                        [TourGroupScheduleID], [ScheduleMethod], 
                                        [ArrivalTime], [LeaveTime], [BreakMethod], 
                                        [BigBedRoomNum], [SingleRoomNum], [DoubleRoomNum],
                                        [ThreeRoomNum], [AccompanyRoomNum],
                                        [SpecialRoom], [ContractPath],[StayOver],
                                        [ExtraBedNum],[BigBedRoomUnitPrice],[SingleRoomUnitPrice],
                                        [DoubleRoomUnitPrice],[ThreeRoomUnitPrice],[AccompanyRoomUnitPrice],
                                        [ExtraBedUnitPrice],[OrderState],[ProviderID],[ProviderName])
                                        VALUES({0},'{1}',{2}, '{3}','{4}','{5}','{6}', {7}, {8},{9},{10}, {11}, '{12}','{13}',{14},{15},{16},{17},{18},{19},{20},{21},'{22}',{23},'{24}')",
                                       expenseID,//hotelEntity.ExpenseID,
                                       hotelEntity.TourgroupID,
                                       hotelEntity.TourGroupScheduleID,
                                       hotelEntity.ScheduleMethod,
                                       hotelEntity.ArrivalTime,
                                       hotelEntity.LeaveTime,
                                       hotelEntity.BreakMethod,
                                       hotelEntity.BigBedRoomNum,
                                       hotelEntity.SingleRoomNum,
                                       hotelEntity.DoubleRoomNum,
                                       hotelEntity.ThreeRoomNum,
                                       hotelEntity.AccompanyRoomNum,
                                       hotelEntity.SpecialRoom,
                                       hotelEntity.ContractPath,
                                       hotelEntity.StayOver,
                                       hotelEntity.ExtraBedNum,
                                       hotelEntity.BigBedRoomUnitPrice,
                                       hotelEntity.SingleRoomUnitPrice,
                                       hotelEntity.DoubleRoomUnitPrice,
                                       hotelEntity.ThreeRoomUnitPrice,
                                       hotelEntity.AccompanyRoomUnitPrice,
                                       hotelEntity.ExtraBedUnitPrice,
                                       hotelEntity.OrderState,
                                       hotelEntity.ProviderID,
                                       hotelEntity.ProviderName);
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }

        /// <summary>
        /// 根据指定的行程计划号，宾馆预定信息数据创建
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        internal static int CreateHotelOrderData(HotelOrderEntity hotelEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlHotel = string.Format(@"INSERT INTO fee_HotelOrder (
                                        [ExpenseID], [TourgroupID],
                                        [TourGroupScheduleID], [ScheduleMethod], 
                                        [ArrivalTime], [LeaveTime], [BreakMethod], 
                                        [BigBedRoomNum], [SingleRoomNum], [DoubleRoomNum],
                                        [ThreeRoomNum], [AccompanyRoomNum],
                                        [SpecialRoom], [ContractPath],[StayOver],
                                        [ExtraBedNum],[BigBedRoomUnitPrice],[SingleRoomUnitPrice],
                                        [DoubleRoomUnitPrice],[ThreeRoomUnitPrice],[AccompanyRoomUnitPrice],
                                        [ExtraBedUnitPrice],[OrderState],[ProviderID],[ProviderName])
                                        VALUES({0},'{1}',{2}, '{3}','{4}','{5}','{6}', {7}, {8},{9},{10}, {11}, '{12}','{13}',{14},{15},{16},{17},{18},{19},{20},{21},'{22}',{23},'{24}')",
                                       -1,//hotelEntity.ExpenseID,
                                       hotelEntity.TourgroupID,
                                       hotelEntity.TourGroupScheduleID,
                                       hotelEntity.ScheduleMethod,
                                       hotelEntity.ArrivalTime,
                                       hotelEntity.LeaveTime,
                                       hotelEntity.BreakMethod,
                                       hotelEntity.BigBedRoomNum,
                                       hotelEntity.SingleRoomNum,
                                       hotelEntity.DoubleRoomNum,
                                       hotelEntity.ThreeRoomNum,
                                       hotelEntity.AccompanyRoomNum,
                                       hotelEntity.SpecialRoom,
                                       hotelEntity.ContractPath,
                                       hotelEntity.StayOver,
                                       hotelEntity.ExtraBedNum,
                                       hotelEntity.BigBedRoomUnitPrice,
                                       hotelEntity.SingleRoomUnitPrice,
                                       hotelEntity.DoubleRoomUnitPrice,
                                       hotelEntity.ThreeRoomUnitPrice,
                                       hotelEntity.AccompanyRoomUnitPrice,
                                       hotelEntity.ExtraBedUnitPrice,
                                       hotelEntity.OrderState,
                                       hotelEntity.ProviderID,
                                       hotelEntity.ProviderName);
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }


        /// <summary>
        /// 更改宾馆预订的订单状态
        /// </summary>
        /// <param name="HotelOrderID">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        internal static int UpdateHotelOrderStateData(string HotelOrderID, OrderStateEnum orderState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fee_HotelOrder SET [OrderState]='{0}' WHERE HotelOrderID={1}", orderState, HotelOrderID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据指定的行程计划号，更新宾馆预定和费用信息
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int UpdateHotelOrderandExpenseData(ExpenseEntity expenseEntity, HotelOrderEntity hotelEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0}, [ProviderName]='{1}',
                                        [Budget]={2}, [ServiceName]='{3}',[Status]='{4}'
                                        WHERE ExpenseID={5}",
                                       expenseEntity.ProviderID,
                                       expenseEntity.ProviderName,
                                       expenseEntity.Budget,
                                       expenseEntity.ServiceName,
                                       expenseEntity.Status,
                                       expenseEntity.ExpenseID
                                    );
            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlHotel = string.Format(@"UPDATE fee_HotelOrder SET
                                        [TourgroupID]='{0}',
                                        [AccompanyRoomNum]={1},                        [ArrivalTime]='{2}',
                                        [BigBedRoomNum]={3},
                                        [BreakMethod]='{4}',                           [ContractPath]='{5}',
                                        [DoubleRoomNum]={6},                           [TourGroupScheduleID]={7},
                                        [LeaveTime]='{8}',                             [ScheduleMethod]='{9}',
                                        [SingleRoomNum]={10},                          [SpecialRoom]='{11}',
                                        [ThreeRoomNum]={12},                           [StayOver]={13},
                                        [ExtraBedNum]={14},                            [BigBedRoomUnitPrice]={15},
                                        [SingleRoomUnitPrice]={16},                    [DoubleRoomUnitPrice]={17},
                                        [ThreeRoomUnitPrice]={18},                     [AccompanyRoomUnitPrice]={19},
                                        [ExtraBedUnitPrice]={20},[ProviderID]={21},[ProviderName]='{22}'
                                        WHERE [ExpenseID]={23}",
                                        hotelEntity.TourgroupID,
                                        hotelEntity.AccompanyRoomNum,
                                        hotelEntity.ArrivalTime,
                                        hotelEntity.BigBedRoomNum,
                                        hotelEntity.BreakMethod,
                                        hotelEntity.ContractPath,
                                        hotelEntity.DoubleRoomNum,
                                        hotelEntity.TourGroupScheduleID,
                                        hotelEntity.LeaveTime,
                                        hotelEntity.ScheduleMethod,
                                        hotelEntity.SingleRoomNum,
                                        hotelEntity.SpecialRoom,
                                        hotelEntity.ThreeRoomNum,
                                        hotelEntity.StayOver,
                                        hotelEntity.ExtraBedNum,
                                        hotelEntity.BigBedRoomUnitPrice,
                                        hotelEntity.SingleRoomUnitPrice,
                                        hotelEntity.DoubleRoomUnitPrice,
                                        hotelEntity.ThreeRoomUnitPrice,
                                        hotelEntity.AccompanyRoomUnitPrice,
                                        hotelEntity.ExtraBedUnitPrice,
                                        hotelEntity.ProviderID,
                                        hotelEntity.ProviderName,
                                        hotelEntity.ExpenseID);
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }

        /// <summary>
        /// 根据指定的行程计划号，更新宾馆预定和创建费用信息
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int UpdateHotelOrderAndCreateExpenseData(ExpenseEntity expenseEntity, HotelOrderEntity hotelEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expenseEntity.TourgroupID,
                                        expenseEntity.ProviderID,
                                        expenseEntity.ProviderName,
                                        expenseEntity.ServiceName,
                                        expenseEntity.Budget,
                                        expenseEntity.PayTotal,
                                        expenseEntity.ApplyPayTotal,
                                        expenseEntity.AdjustAmount,
                                        expenseEntity.Status,
                                        expenseEntity.TypeBelong,
                                        expenseEntity.ApplyPay,
                                        expenseEntity.PayStatus,
                                        expenseEntity.TourgroupName,
                                        expenseEntity.CreateByUserName,
                                        expenseEntity.TourGroupScheduleID,
                                        expenseEntity.CreateTime,
                                        expenseEntity.ExpenseTypeID,
                                        expenseEntity.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlHotel = string.Format(@"UPDATE fee_HotelOrder SET
                                        [TourgroupID]='{0}',
                                        [AccompanyRoomNum]={1},                        [ArrivalTime]='{2}',
                                        [BigBedRoomNum]={3},
                                        [BreakMethod]='{4}',                           [ContractPath]='{5}',
                                        [DoubleRoomNum]={6},                           [TourGroupScheduleID]={7},
                                        [LeaveTime]='{8}',                             [ScheduleMethod]='{9}',
                                        [SingleRoomNum]={10},                          [SpecialRoom]='{11}',
                                        [ThreeRoomNum]={12},                           [StayOver]={13},
                                        [ExtraBedNum]={14},                            [BigBedRoomUnitPrice]={15},
                                        [SingleRoomUnitPrice]={16},                    [DoubleRoomUnitPrice]={17},
                                        [ThreeRoomUnitPrice]={18},                     [AccompanyRoomUnitPrice]={19},
                                        [ExtraBedUnitPrice]={20},[ProviderID]={21},[ProviderName]='{22}',[ExpenseID]={23}
                                        WHERE [HotelOrderID]={24}",
                                        hotelEntity.TourgroupID,
                                        hotelEntity.AccompanyRoomNum,
                                        hotelEntity.ArrivalTime,
                                        hotelEntity.BigBedRoomNum,
                                        hotelEntity.BreakMethod,
                                        hotelEntity.ContractPath,
                                        hotelEntity.DoubleRoomNum,
                                        hotelEntity.TourGroupScheduleID,
                                        hotelEntity.LeaveTime,
                                        hotelEntity.ScheduleMethod,
                                        hotelEntity.SingleRoomNum,
                                        hotelEntity.SpecialRoom,
                                        hotelEntity.ThreeRoomNum,
                                        hotelEntity.StayOver,
                                        hotelEntity.ExtraBedNum,
                                        hotelEntity.BigBedRoomUnitPrice,
                                        hotelEntity.SingleRoomUnitPrice,
                                        hotelEntity.DoubleRoomUnitPrice,
                                        hotelEntity.ThreeRoomUnitPrice,
                                        hotelEntity.AccompanyRoomUnitPrice,
                                        hotelEntity.ExtraBedUnitPrice,
                                        hotelEntity.ProviderID,
                                        hotelEntity.ProviderName,
                                        expenseID,
                                        hotelEntity.HotelOrderID);
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }
        /// <summary>
        /// 根据指定的行程计划号，更新宾馆预定信息
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int DeleteHotelOrderDataExpense(HotelOrderEntity hotelEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlHotel = string.Format(@"UPDATE fee_HotelOrder SET
                                        [TourgroupID]='{0}',
                                        [AccompanyRoomNum]={1},                        [ArrivalTime]='{2}',
                                        [BigBedRoomNum]={3},
                                        [BreakMethod]='{4}',                           [ContractPath]='{5}',
                                        [DoubleRoomNum]={6},                           [TourGroupScheduleID]={7},
                                        [LeaveTime]='{8}',                             [ScheduleMethod]='{9}',
                                        [SingleRoomNum]={10},                          [SpecialRoom]='{11}',
                                        [ThreeRoomNum]={12},                           [StayOver]={13},
                                        [ExtraBedNum]={14},                            [BigBedRoomUnitPrice]={15},
                                        [SingleRoomUnitPrice]={16},                    [DoubleRoomUnitPrice]={17},
                                        [ThreeRoomUnitPrice]={18},                     [AccompanyRoomUnitPrice]={19},
                                        [ExtraBedUnitPrice]={20},[ProviderID]={21},[ProviderName]='{22}',[ExpenseID]={23}
                                        WHERE [HotelOrderID]={24}",
                                        hotelEntity.TourgroupID,
                                        hotelEntity.AccompanyRoomNum,
                                        hotelEntity.ArrivalTime,
                                        hotelEntity.BigBedRoomNum,
                                        hotelEntity.BreakMethod,
                                        hotelEntity.ContractPath,
                                        hotelEntity.DoubleRoomNum,
                                        hotelEntity.TourGroupScheduleID,
                                        hotelEntity.LeaveTime,
                                        hotelEntity.ScheduleMethod,
                                        hotelEntity.SingleRoomNum,
                                        hotelEntity.SpecialRoom,
                                        hotelEntity.ThreeRoomNum,
                                        hotelEntity.StayOver,
                                        hotelEntity.ExtraBedNum,
                                        hotelEntity.BigBedRoomUnitPrice,
                                        hotelEntity.SingleRoomUnitPrice,
                                        hotelEntity.DoubleRoomUnitPrice,
                                        hotelEntity.ThreeRoomUnitPrice,
                                        hotelEntity.AccompanyRoomUnitPrice,
                                        hotelEntity.ExtraBedUnitPrice,
                                        hotelEntity.ProviderID,
                                        hotelEntity.ProviderName,
                                        -1,
                                        hotelEntity.HotelOrderID);
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }
        /// <summary>
        /// 复制团计划所属的宾馆信息到新的团
        /// </summary>
        /// <param name="newTourGroupId"></param>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        internal static bool CopyHotelOrder(string newTourGroupId, string newtourgroupName, string oldTourGroupId, int newAndOldDifferDays)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DataTable dtNewSchedule = TeamGroupCtrl.GetTourGroupSchedule(newTourGroupId).Tables[0];//获取新团的计划号数据
            DataTable dtOldSchedule = TeamGroupCtrl.GetTourGroupSchedule(oldTourGroupId).Tables[0];//获取旧团的计划号数据
            for (int i = 0; i < dtOldSchedule.Rows.Count; i++)
            {
                ExpenseEntity eEntity = new ExpenseEntity();
                HotelOrderEntity hEntity = new HotelOrderEntity();
                string oldScheduleId = dtOldSchedule.Rows[i]["TourGroupScheduleID"].ToString();
                string newScheduleId = dtNewSchedule.Rows[i]["TourGroupScheduleID"].ToString();

                foreach (DataRow EOrderDr in HotelOrderCtrl.GetHotelExpenseAndOrderData(oldScheduleId).Rows)
                {
                    DateTime arrivalTime = DateTime.Parse(EOrderDr["ArrivalTime"].ToString()).AddDays(newAndOldDifferDays);
                    DateTime leaveTime = DateTime.Parse(EOrderDr["LeaveTime"].ToString()).AddDays(newAndOldDifferDays);
                    //费用信息
                    eEntity.AdjustAmount = 0;// decimal.Parse(EOrderDr["AdjustAmount"].ToString());
                    eEntity.ApplyPay = 0;// decimal.Parse(EOrderDr["ApplyPay"].ToString());
                    eEntity.ApplyPayTotal = 0;// decimal.Parse(EOrderDr["ApplyPayTotal"].ToString());
                    eEntity.Budget = decimal.Parse(EOrderDr["Budget"].ToString());
                    eEntity.CreateByUserName = Thread.CurrentPrincipal.Identity.Name;
                    eEntity.CreateTime = DateTime.Now.ToString("yyyy-MM-dd");
                    //eEntity.ExpenseID = int.Parse(EOrderDr["AccompanyRoomNum"].ToString());
                    eEntity.ExpenseTypeID = int.Parse(EOrderDr["ExpenseTypeID"].ToString());
                    eEntity.PayStatus = PayApplyStatusEnum.未付.ToString();//EOrderDr["PayStatus"].ToString();
                    eEntity.PayTotal = 0;// decimal.Parse(EOrderDr["PayTotal"].ToString());
                    eEntity.ProviderID = int.Parse(EOrderDr["ProviderID"].ToString());
                    eEntity.ProviderName = EOrderDr["ProviderName"].ToString();
                    eEntity.Remark = EOrderDr["Remark"].ToString();
                    eEntity.ServiceName = EOrderDr["ServiceName"].ToString();
                    eEntity.Status = ExpenseStatusEnum.计划.ToString();//EOrderDr["Status"].ToString();
                    eEntity.TourgroupID = newTourGroupId;
                    eEntity.TourgroupName = newtourgroupName;
                    eEntity.TourGroupScheduleID = int.Parse(newScheduleId);
                    eEntity.TypeBelong = EOrderDr["TypeBelong"].ToString();
                    eEntity.WipeOffMethod = EOrderDr["WipeOffMethod"].ToString();
                    //预订信息
                    hEntity.AccompanyRoomNum = int.Parse(EOrderDr["AccompanyRoomNum"].ToString());
                    hEntity.AccompanyRoomUnitPrice = decimal.Parse(EOrderDr["AccompanyRoomUnitPrice"].ToString());
                    hEntity.ArrivalTime = arrivalTime.ToString("yyyy-MM-dd");
                    hEntity.BigBedRoomNum = int.Parse(EOrderDr["BigBedRoomNum"].ToString());
                    hEntity.BigBedRoomUnitPrice = decimal.Parse(EOrderDr["BigBedRoomUnitPrice"].ToString());
                    hEntity.BreakMethod = EOrderDr["BreakMethod"].ToString();
                    hEntity.ContractPath = EOrderDr["ContractPath"].ToString();
                    hEntity.DoubleRoomNum = int.Parse(EOrderDr["DoubleRoomNum"].ToString());
                    hEntity.DoubleRoomUnitPrice = decimal.Parse(EOrderDr["DoubleRoomUnitPrice"].ToString());
                    //hEntity.ExpenseID = int.Parse(EOrderDr["ExpenseID"].ToString());
                    hEntity.ExtraBedNum = int.Parse(EOrderDr["ExtraBedNum"].ToString());
                    hEntity.ExtraBedUnitPrice = decimal.Parse(EOrderDr["ExtraBedUnitPrice"].ToString());
                    hEntity.LeaveTime = leaveTime.ToString("yyyy-MM-dd");
                    hEntity.OrderState = EOrderDr["OrderState"].ToString();
                    hEntity.ScheduleMethod = EOrderDr["ScheduleMethod"].ToString();
                    hEntity.SingleRoomNum = int.Parse(EOrderDr["SingleRoomNum"].ToString());
                    hEntity.SingleRoomUnitPrice = decimal.Parse(EOrderDr["SingleRoomUnitPrice"].ToString());
                    hEntity.SpecialRoom = EOrderDr["SpecialRoom"].ToString();
                    hEntity.StayOver = (leaveTime - arrivalTime).Days;//int.Parse(EOrderDr["StayOver"].ToString());
                    hEntity.ThreeRoomNum = int.Parse(EOrderDr["ThreeRoomNum"].ToString());
                    hEntity.ThreeRoomUnitPrice = decimal.Parse(EOrderDr["ThreeRoomUnitPrice"].ToString());
                    hEntity.TourgroupID = newTourGroupId;
                    hEntity.TourGroupScheduleID = int.Parse(newScheduleId);
                    CreateHotelOrderAndExpenseData(eEntity, hEntity);
                }
            }
            return true;
        }
        /// <summary>
        /// 根据指定的计划号获取一条宾馆费用及预订信息
        /// </summary>
        /// <param name="scheduleId"></param>
        private static DataTable GetHotelExpenseAndOrderData(string scheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string Sql = string.Format(@"SELECT h.*,e.* FROM [fee_HotelOrder] h,fee_Expense e WHERE e.ExpenseID=h.ExpenseID AND e.TourGroupScheduleID={0}",scheduleId);
            DbCommand dbCommand = db.GetSqlStringCommand(Sql);
            
            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 删除指定计划号的宾馆预定信息
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public static int DeleteHotelOrder(int tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_HotelOrder where TourGroupScheduleID={0}", tourGroupScheduleID);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);
            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_HotelOrder WHERE TourGroupScheduleID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
                                         tourGroupScheduleID,
                                         expenseObj);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 宾馆预订单查询

        internal static DataTable GetBatchHotelOrderData(string tourGroupId, string providerId, DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            
            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string szCondition = "e.Status <>'" + ExpenseStatusEnum.取消 + "'";

            if (tourGroupId != "")
                szCondition = " h.TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND e.ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " e.ProviderID='" + providerId + "'";
            }
            if (szCondition != "")
            {
                szCondition += string.Format(" AND (ArrivalTime>='{0}' AND ArrivalTime<='{1}') AND h.expenseid=e.expenseid AND h.TourgroupID=t.TourgroupID ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            }
            else
            {
                szCondition += string.Format(" (ArrivalTime>='{0}' AND ArrivalTime<='{1}') AND h.expenseid=e.expenseid  AND h.TourgroupID=t.TourgroupID ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            }

            string Sql = @"SELECT h.[HotelOrderID],  h.[TourgroupID], h.[ScheduleMethod], h.[ArrivalTime], h.[LeaveTime], h.[BreakMethod],h.[OrderState],
                                  h.[BigBedRoomNum],h.[SingleRoomNum],h.[DoubleRoomNum], h.[ThreeRoomNum],h.[AccompanyRoomNum],h.[SpecialRoom],h.[StayOver],
                                  t.[TourgroupName],t.[AdultNum],t.[AccompanyNum],(h.[BigBedRoomNum]+h.[SingleRoomNum]+h.[DoubleRoomNum]+h.[ThreeRoomNum]+h.[AccompanyRoomNum]) as TotalRoomNum, 
                                  e.[ProviderID],e.[ProviderName] 
                                  FROM [fee_HotelOrder] h,orgt_TourGroup t,fee_Expense e";
            if (szCondition != "")
                Sql += " WHERE " + szCondition;

//            string sql = "";
//            if (tourGroupId == "")
//            {
//                if (providerId == "")
//                {
//                    sql = string.Format(@"SELECT h.[HotelOrderID],  h.[TourgroupID], h.[ScheduleMethod], h.[ArrivalTime], h.[LeaveTime], h.[BreakMethod],
//                                                h.[BigBedRoomNum],h.[SingleRoomNum],h.[DoubleRoomNum], h.[ThreeRoomNum],h.[AccompanyRoomNum],h.[SpecialRoom],h.[StayOver],
//                                                e.[ProviderID],e.[ProviderName],e.[Budget]
//                                                FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID 
//                                                        AND (ArrivalTime>=@startTime AND ArrivalTime<=@endTime)");
//                    dbCommand = db.GetSqlStringCommand(sql);
//                    db.AddParameter(dbCommand, "@startTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, startDate.ToString("yyyy-MM-dd"));
//                    db.AddParameter(dbCommand, "@endTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, endDate.ToString("yyyy-MM-dd"));

//                }
//                else
//                {
//                    sql = string.Format(@"SELECT h.[HotelOrderID],  h.[TourgroupID], h.[ScheduleMethod], h.[ArrivalTime], h.[LeaveTime], h.[BreakMethod],
//                                                h.[BigBedRoomNum],h.[SingleRoomNum],h.[DoubleRoomNum], h.[ThreeRoomNum],h.[AccompanyRoomNum],h.[SpecialRoom],h.[StayOver],
//                                                e.[ProviderID],e.[ProviderName],e.[Budget]
//                                                FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID 
//                                                        AND e.ProviderID=@ProviderID AND(ArrivalTime>=@startTime AND ArrivalTime<=@endTime) ");
//                    dbCommand = db.GetSqlStringCommand(sql);
//                    db.AddParameter(dbCommand, "@ProviderID", DbType.Int32, ParameterDirection.Input, "", DataRowVersion.Default, providerId);
//                    db.AddParameter(dbCommand, "@startTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, startDate.ToString("yyyy-MM-dd"));
//                    db.AddParameter(dbCommand, "@endTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, endDate.ToString("yyyy-MM-dd"));
//                }
//            }
//            else if (providerId == "")
//            {
//                sql = string.Format(@"SELECT h.[HotelOrderID],  h.[TourgroupID], h.[ScheduleMethod], h.[ArrivalTime], h.[LeaveTime], h.[BreakMethod],
//                                                h.[BigBedRoomNum],h.[SingleRoomNum],h.[DoubleRoomNum], h.[ThreeRoomNum],h.[AccompanyRoomNum],h.[SpecialRoom],h.[StayOver],
//                                                e.[ProviderID],e.[ProviderName],e.[Budget]
//                                                FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID 
//                                                       AND h.TourGroupID=@TourGroupID AND(ArrivalTime>=@startTime AND ArrivalTime<=@endTime) ");
//                dbCommand = db.GetSqlStringCommand(sql);
//                db.AddParameter(dbCommand, "@TourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupId);
//                db.AddParameter(dbCommand, "@startTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, startDate.ToString("yyyy-MM-dd"));
//                db.AddParameter(dbCommand, "@endTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, endDate.ToString("yyyy-MM-dd"));
//            }
//            else
//            {
//                sql = string.Format(@"SELECT h.[HotelOrderID],  h.[TourgroupID], h.[ScheduleMethod], h.[ArrivalTime], h.[LeaveTime], h.[BreakMethod],
//                                                h.[BigBedRoomNum],h.[SingleRoomNum],h.[DoubleRoomNum], h.[ThreeRoomNum],h.[AccompanyRoomNum],h.[SpecialRoom],h.[StayOver],
//                                                e.[ProviderID],e.[ProviderName],e.[Budget]
//                                                FROM [fee_HotelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID 
//                                                       AND h.TourGroupID=@TourGroupID AND e.ProviderID=@ProviderID AND(ArrivalTime>=@startTime AND ArrivalTime<=@endTime) ");
//                dbCommand = db.GetSqlStringCommand(sql);
//                db.AddParameter(dbCommand, "@TourGroupID", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, tourGroupId);
//                db.AddParameter(dbCommand, "@ProviderID", DbType.Int32, ParameterDirection.Input, "", DataRowVersion.Default, providerId);
//                db.AddParameter(dbCommand, "@startTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, startDate.ToString("yyyy-MM-dd"));
//                db.AddParameter(dbCommand, "@endTime", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, endDate.ToString("yyyy-MM-dd"));
//            }
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

    }
}
